IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_ProjectSummary_BudgetByProject')
	BEGIN
		DROP  Procedure  up_Report_ProjectSummary_BudgetByProject
	END

GO

CREATE Procedure up_Report_ProjectSummary_BudgetByProject
	@ProjectId int = null
AS

--HANDLE OLD VERSION
IF (@ProjectId IS NULL)
BEGIN
	SET @ProjectId = 6
END

/*
DECLARE @EstimatedPartial money
DECLARE @EstimatedVisual money

SELECT 
	@EstimatedPartial = EstimatedPartial,
	@EstimatedVisual = EstimatedVisual 	
FROM 
	uv_TotalEstimatedAssetsByInspectionType 
WHERE 
	ProjectId = @ProjectId
*/

--Get Estimated Counts and prices
DECLARE @EstimatedPartial int
DECLARE @EstimatedVisual int
DECLARE @EstimatedFull int
DECLARE @EstimatedGuyGuards int
DECLARE @EstimatedGroundWire int
DECLARE @EstimatedPoleTag int

DECLARE @PartialPrice MONEY
DECLARE @VisualPrice MONEY
DECLARE @FullPrice MONEY
DECLARE @GuyGuardsPrice MONEY
DECLARE @GroundWirePrice MONEY
DECLARE @PoleTagPrice MONEY


SELECT 
	 @EstimatedPartial = EstimatedPartialCount
	,@EstimatedVisual = EstimatedVisualCount
	,@EstimatedFull = EstimatedFullCount
	,@EstimatedGuyGuards = EstimatedGuyGuardsCount
	,@EstimatedGroundWire = EstimatedGroundWireCount
	,@EstimatedPoleTag = EstimatedPoleTagCount
	,@PartialPrice = PartialUnitPrice
	,@VisualPrice = VisualUnitPrice
	,@FullPrice = FullUnitPrice
	,@GuyGuardsPrice = GuyGuardsUnitPrice
	,@GroundWirePrice = GroundWireUnitPrice
	,@PoleTagPrice =PoleTagUnitPrice
FROM	
	tb_vendor_projects
WHERE 
	Vendor_ProjectId = @ProjectId

SELECT 
	1 AS 'Order',
	T.Name,
	Count(R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk) as InspectionAmounts ,
	R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk as InspectionTypeFk,
	@projectId as ProjectId,
	(CASE R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
		WHEN  1 THEN (@EstimatedFull)
		WHEN  2 THEN (@EstimatedPartial)
		WHEN  3 THEN (@EstimatedVisual)
	END) AS Estimated,
	(CASE R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
		WHEN  1 THEN CAST(@FullPrice as nvarchar) 
		WHEN  2 THEN CAST(@PartialPrice as nvarchar) 
		WHEN  3 THEN CAST(@VisualPrice as nvarchar) 
	END) AS UnityValue,
	(CASE R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
		WHEN  1 THEN COUNT(R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk) * @FullPrice
		WHEN  2 THEN COUNT(R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk) * @PartialPrice
		WHEN  3 THEN COUNT(R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk) * @VisualPrice
	END) AS subTotal,
	(CASE R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
		WHEN  1 THEN @EstimatedFull * @FullPrice
		WHEN  2 THEN @EstimatedPartial * @PartialPrice
		WHEN  3 THEN @EstimatedVisual * @VisualPrice
	END) AS subTotalEstimated
FROM
	tb_Vendor_InspVisit_XmissionStructures_Poles P 
LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Results R 
	ON P.Vendor_InspVisit_XMissionStructure_PoleId = R.Vendor_InspVisit_XMissionStructure_PoleFk
LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Inspection_Type T 
	ON R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk = T.Vendor_InspVisit_XmissionStructures_Inspection_TypeId
WHERE 
	--P.Utility_Asset_XMissionStructures_PoleFk IS NOT NULL AND
	P.Vendor_ProjectFk = @ProjectId
GROUP BY
	R.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk, T.Name

UNION

--Gets Ground Wire results
SELECT
	4 AS 'Order' 	
	,'Ground Wire Repair' as 'Name'
	,COUNT(*) 'InspectionAmounts'
	,NULL AS InspectionTypeFk
	,@ProjectId as 'ProjectId'
	,@EstimatedGroundWire  AS 'Estimated'
	,@GroundWirePrice AS 'UnityValue'
	,COUNT(*) * @GroundWirePrice AS 'SubTotal'
	,@EstimatedGroundWire * @GroundWirePrice AS 'SubTotalEstimated'
FROM 
	tb_Vendor_InspVisit_XmissionStructures_Poles P
WHERE 
	P.Vendor_ProjectFk = @ProjectId
	AND P.GroundWireRepair = 1

UNION

--Gets Guy Guard Results
SELECT
	5 AS 'Order' 	
	,'Guy Guard' as 'Name'
	,SUM(GuardsQuantity) 'InspectionAmounts'
	,NULL AS InspectionTypeFk
	,@ProjectId as 'ProjectId'
	,@EstimatedGuyGuards AS 'Estimated'
	,@GuyGuardsPrice AS 'UnityValue'
	,SUM(GuardsQuantity) * @GuyGuardsPrice AS 'SubTotal'
	,@EstimatedGuyGuards * @GuyGuardsPrice AS 'SubTotalEstimated'
FROM 
	tb_Vendor_InspVisit_XmissionStructures_Poles P
WHERE 
	P.Vendor_ProjectFk = @ProjectId
	AND P.Billable = 1

UNION

--Gets Pole Tag results
SELECT
	6 AS 'Order' 	
	,'Pole Tag Id' as 'Name'
	,COUNT(*) 'InspectionAmounts'
	,NULL AS InspectionTypeFk
	,@ProjectId as 'ProjectId'
	,@EstimatedPoleTag AS 'Estimated'
	,@PoleTagPrice AS 'UnityValue'
	,COUNT(*) * @PoleTagPrice AS 'SubTotal'
	,@EstimatedPoleTag * @PoleTagPrice AS 'SubTotalEstimated'
FROM 
	tb_Vendor_InspVisit_XmissionStructures_Poles P
WHERE 
	P.Vendor_ProjectFk = @ProjectId
	AND P.PoleTag = 1

ORDER BY 1


GO

GRANT EXEC ON up_Report_ProjectSummary_BudgetByProject TO PPIReports

GO

